// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');

// 加载MD5模块
// const MD5 = require('md5');

// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'lexiaoqu',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();

server.use(bodyParser.urlencoded({
  extended: false
}));


// 加载CORS模块
const cors = require('cors');

// 使用CORS中间件
server.use(cors({
  origin:'*'
}));
// 获取商品接口
server.get('/product', (req, res) => {
  // SQL语句以获取文章分类表的数据
  let sql = 'SELECT pid,title,pimg,other,content  FROM product_show ORDER BY pid';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
    console.log(results);
  });
});


// 文章详情
server.get('/wenzhang', (req, res) => {
  console.log(req.query);
  // SQL语句以获取文章分类表的数据
  let sql = 'SELECT nid,ntitle,nimg,content  FROM news WHERE nid=?';
  // 执行SQL语句
  pool.query(sql, [req.query.nid],(error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
    console.log(results);
  });
});

// 获取门店接口
server.get('/md', (req, res) => {
  // SQL语句以获取文章分类表的数据
  let sql = 'SELECT id,name,xq,dt,xt,dz  FROM md ORDER BY id';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
    console.log(results);
  });
});

// 获取所有新闻的接口
server.get('/news', (req, res) => {
  // 获取客户端传递的page参数
  let page = req.query.page? req.query.page : 1;
let nid
  // 存储每页显示的记录数
  let pagesize = 5;

  // 通过公式来计算从第几条记录开始返回
  let offset = (page - 1) * pagesize;

  // 用于存储获取到的总记录数
  let rowcount;

  // 获取指定分类下的文章总数
  let sql = 'SELECT COUNT(nid) AS count FROM news ';

  pool.query(sql, [nid], (error, results) => {
    if (error) throw error;
    // 将获取到总记录数赋给rowcount变量
    rowcount = results[0].count;
    /**************************************************/
    // 根据总记录数和每页显示的记录数来计算总页数
    let pagecount = Math.ceil(rowcount / pagesize);

    // 查询SQL语句
   let sql = 'SELECT nid,ntitle,nimg,content FROM news ORDER BY nid LIMIT ?,?';
 
    // 执行SQL
    pool.query(sql, [offset, pagesize], (error, results) => {
      console.log(offset,pagesize);
      if (error) throw error;
      res.send({ message: 'ok', code: 200, results: results, pagecount: pagecount  ,offset:offset,page:page});
    });

   
  });

});
// 用户注册接口
server.post('/register', (req, res) => {
  //console.log(md5('12345678'));
  // 获取用户名和密码信息
  let username = req.body.username;
  let password = req.body.password;
  let phone = req.body.phone
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT COUNT(username) AS count FROM user WHERE username=?';
  pool.query(sql, [username], (error, results) => {
    // console.log('qqq',results)
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT user(username,password,phone ) VALUES(?,?,?)';
      pool.query(sql, [username, password,phone], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200});
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  });
});

// 用户登录接口

server.post('/login',(req,res,next)=>{
	  let username = req.body.username;
  let pwd = req.body.pwd;
  // console.log(username,pwd);
        pool.query('select username,password from user where username=? and password=?',[username,pwd],(err,result)=>{
		if (err){
		next(err);
	};
    console.log('qq',result);
		if (result.length===0)
		{res.send({code:501,msg:'登录失败'})
		}else{
		res.send({code:200,msg:'登录成功'})
		}
});	});
// 指定服务器对象监听的端口号
server.listen(3000, () => {
  console.log('server is running...');
});